﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Windows.Forms;

namespace TMS
{
    class Project
    {
        public int ID;
        public string Name;
        public string Start_Date;
        public string End_Date;
        public string Status;
        public string Contributor;
        public string Responsible;

        public Project()
        { 
        }

        public DataSet GetAllProjectsFromDB()
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            

            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();


            string sqlSelectAll = "SELECT * from Projects";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);
            mysqlCon.Close();
            return table;
            
        }

        public void AddtoDB()
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;


            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();

            string sqlInsert = "INSERT INTO Projects (`ID`, `Name`, `Start_Date`, `End_Date`, `Status`, `Partner`,`Responsible`) VALUES (DEFAULT, '" + this.Name + "', '" + this.Start_Date.ToString() + "', '" + this.End_Date.ToString() + "', '" + this.Status + "', '" + this.Contributor + "','"+this.Responsible+"')";

            
            MySqlCommand cmd = new MySqlCommand(sqlInsert, mysqlCon);
            cmd.ExecuteNonQuery();

            mysqlCon.Close();

        }

        public void DeleteProjectFromDB()
        {

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlDeleteProduct = "DELETE FROM projects " +
                                      "WHERE ID = " + this.ID;

            MySqlCommand cmd = new MySqlCommand(sqlDeleteProduct, mysqlCon);
            cmd.ExecuteNonQuery();

            mysqlCon.Close();
        }

        public void UpdateProductInDB()
        {

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlUpdateProduct = "UPDATE projects " +
                                      "SET Name ='" + this.Name + "'," +
                                      "Start_Date ='" + this.Start_Date.ToString() + "', " +
                                      "End_Date ='" + this.End_Date.ToString() + "', " +
                                      "Status ='" + this.Status + "', " +
                                      "Partner ='" + this.Contributor + "', " +
                                      "Responsible ='" + this.Responsible + "' " +
                                      "WHERE ID = " + this.ID;

           
            MySqlCommand cmd = new MySqlCommand(sqlUpdateProduct, mysqlCon);

            cmd.ExecuteNonQuery();

            mysqlCon.Close();

        }

        public string GetName()
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;


            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();


            string sqlSelectAll = "SELECT * from Projects WHERE ID="+this.ID;
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);
            mysqlCon.Close();
            this.Name = table.Tables[0].Rows[0].ItemArray[1].ToString();

            return this.Name;
        }

        public DataTable GetPartner()
        {
        DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;


            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();


           // string sqlSelectAll = "SELECT name,address,city from partners WHERE name="+this.ID;
            string sqlSelectAll = "SELECT partners.name,partners.address,partners.city FROM partners LEFT JOIN projects ON projects.Partner=partners.name WHERE projects.ID='" +this.ID+ "'";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);
            mysqlCon.Close();

            return table.Tables[0];
        }
    }
}
